/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50743
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50743
 File Encoding         : 65001

 Date: 06/07/2024 21:04:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
  `admin_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '管理员编号',
  `admin_name` varchar(20) NOT NULL COMMENT '管理员姓名',
  `admin_password` varchar(100) DEFAULT NULL COMMENT '密码',
  `token` varchar(255) DEFAULT NULL,
  `create_time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`admin_id`),
  UNIQUE KEY `user_username_uindex` (`admin_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='管理员';

-- ----------------------------
-- Records of admin
-- ----------------------------
BEGIN;
INSERT INTO `admin` VALUES (3, 'admin', 'admin', '3ae2e140-05cd-4579-a450-93312fa08e04', 1720268565157);
COMMIT;

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `gds_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '商品编号',
  `gds_name` varchar(20) NOT NULL COMMENT '商品名称',
  `brand` varchar(20) DEFAULT NULL COMMENT '品牌',
  `model` varchar(20) DEFAULT NULL COMMENT '型号',
  `category` varchar(20) DEFAULT NULL COMMENT '种类',
  `price` float NOT NULL COMMENT '销售单价',
  `amount` int(10) DEFAULT NULL COMMENT '库存数量',
  `gds_status` char(1) DEFAULT '1' COMMENT '状态(1正常/0停产)',
  `unit` varchar(6) DEFAULT NULL COMMENT '单位',
  PRIMARY KEY (`gds_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COMMENT='商品信息';

-- ----------------------------
-- Records of goods
-- ----------------------------
BEGIN;
INSERT INTO `goods` VALUES (1, '冰箱', NULL, NULL, NULL, 1011, 520, '1', '台');
INSERT INTO `goods` VALUES (2, '电饭煲', NULL, NULL, NULL, 800, 300, '1', '台');
INSERT INTO `goods` VALUES (3, '风扇', NULL, NULL, NULL, 59, 666, '1', '台');
INSERT INTO `goods` VALUES (4, '空调', NULL, NULL, NULL, 2000, 100, '1', '台');
INSERT INTO `goods` VALUES (5, '香肠', NULL, NULL, NULL, 6, 999, '1', '6条/包');
INSERT INTO `goods` VALUES (6, '大米', NULL, NULL, NULL, 100, 600, '1', '袋');
INSERT INTO `goods` VALUES (7, '辣条', NULL, NULL, NULL, 2, 1000, '1', '包');
INSERT INTO `goods` VALUES (8, '薯片', NULL, NULL, NULL, 10, 1000, '1', '袋');
INSERT INTO `goods` VALUES (9, '可乐', '可口可乐', NULL, NULL, 4, 3000, '1', '瓶');
INSERT INTO `goods` VALUES (10, '可乐', '百事可乐', NULL, NULL, 4, 3000, '1', '瓶');
INSERT INTO `goods` VALUES (11, '铅笔', NULL, NULL, NULL, 1, 999, '1', '支');
INSERT INTO `goods` VALUES (12, '橡皮擦', NULL, NULL, NULL, 1.5, 600, '1', '块');
COMMIT;

-- ----------------------------
-- Table structure for purchase
-- ----------------------------
DROP TABLE IF EXISTS `purchase`;
CREATE TABLE `purchase` (
  `p_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '进货编号',
  `sp_id` int(10) NOT NULL COMMENT '供货商编号',
  `sf_id` int(10) NOT NULL COMMENT '员工号',
  `p_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '进货日期',
  `gds_id` int(11) DEFAULT NULL,
  `p_price` decimal(10,2) DEFAULT NULL,
  `p_amount` int(10) DEFAULT NULL,
  PRIMARY KEY (`p_id`),
  KEY `purchase_and_supplier` (`sp_id`),
  KEY `purchase_and_staff` (`sf_id`),
  CONSTRAINT `purchase_and_staff` FOREIGN KEY (`sf_id`) REFERENCES `staff` (`sf_id`) ON UPDATE CASCADE,
  CONSTRAINT `purchase_and_supplier` FOREIGN KEY (`sp_id`) REFERENCES `supplier` (`sp_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='进货表';

-- ----------------------------
-- Records of purchase
-- ----------------------------
BEGIN;
INSERT INTO `purchase` VALUES (1, 1, 1, '2024-07-06 12:50:43', 1, 10.00, 20);
COMMIT;

-- ----------------------------
-- Table structure for purchase_record
-- ----------------------------
DROP TABLE IF EXISTS `purchase_record`;
CREATE TABLE `purchase_record` (
  `p_id` int(10) NOT NULL COMMENT '进货编号',
  `gds_id` int(10) NOT NULL COMMENT '商品编号',
  `price` float DEFAULT NULL COMMENT '进货单价',
  `amount` int(5) NOT NULL COMMENT '进货数量',
  PRIMARY KEY (`p_id`,`gds_id`),
  KEY `进货商品信息关联` (`gds_id`),
  KEY `进货编号关联` (`p_id`),
  CONSTRAINT `进货商品信息关联` FOREIGN KEY (`gds_id`) REFERENCES `goods` (`gds_id`),
  CONSTRAINT `进货编号关联` FOREIGN KEY (`p_id`) REFERENCES `purchase` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='进货记录';

-- ----------------------------
-- Records of purchase_record
-- ----------------------------
BEGIN;
INSERT INTO `purchase_record` VALUES (1, 1, 10, 20);
COMMIT;

-- ----------------------------
-- Table structure for sell_item
-- ----------------------------
DROP TABLE IF EXISTS `sell_item`;
CREATE TABLE `sell_item` (
  `sell_id` int(10) NOT NULL COMMENT '销售编号',
  `gds_id` int(10) NOT NULL COMMENT '商品编号',
  `price` float DEFAULT NULL COMMENT '销售单价',
  `amount` int(5) NOT NULL COMMENT '销售数量',
  PRIMARY KEY (`sell_id`,`gds_id`),
  KEY `销售商品信息关联` (`gds_id`),
  KEY `销售成交编号关联` (`sell_id`),
  CONSTRAINT `销售商品信息关联` FOREIGN KEY (`gds_id`) REFERENCES `goods` (`gds_id`),
  CONSTRAINT `销售成交编号关联` FOREIGN KEY (`sell_id`) REFERENCES `sell_record` (`sell_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='销售项';

-- ----------------------------
-- Records of sell_item
-- ----------------------------
BEGIN;
INSERT INTO `sell_item` VALUES (1, 1, 100, 10);
COMMIT;

-- ----------------------------
-- Table structure for sell_record
-- ----------------------------
DROP TABLE IF EXISTS `sell_record`;
CREATE TABLE `sell_record` (
  `sell_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '销售编号',
  `sf_id` int(10) NOT NULL COMMENT '员工编号',
  `sell_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '销售日期',
  PRIMARY KEY (`sell_id`),
  KEY `sell_and_staff` (`sf_id`),
  CONSTRAINT `sell_and_staff` FOREIGN KEY (`sf_id`) REFERENCES `staff` (`sf_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='销售记录';

-- ----------------------------
-- Records of sell_record
-- ----------------------------
BEGIN;
INSERT INTO `sell_record` VALUES (1, 1, '2024-07-06 20:32:36');
COMMIT;

-- ----------------------------
-- Table structure for staff
-- ----------------------------
DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `sf_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `sf_name` varchar(20) NOT NULL COMMENT '员工姓名',
  `identity_id` char(18) NOT NULL COMMENT '身份证号',
  `sf_tel` varchar(12) DEFAULT NULL COMMENT '联系方式',
  `sex` char(2) DEFAULT NULL COMMENT '性别',
  `password` varchar(30) DEFAULT '1234' COMMENT '密码',
  `sf_status` char(1) DEFAULT NULL COMMENT '状态(1在任/0离职)',
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sf_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

-- ----------------------------
-- Records of staff
-- ----------------------------
BEGIN;
INSERT INTO `staff` VALUES (1, '员工001', '1', '2', '1', '1234', '1', NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for supplier
-- ----------------------------
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier` (
  `sp_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '供货商编号',
  `sp_name` varchar(20) NOT NULL COMMENT '供货商名称',
  `location` char(25) DEFAULT NULL COMMENT '所在地',
  `sp_tel` varchar(11) NOT NULL COMMENT '联系方式',
  `description` char(50) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`sp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='供货商信息';

-- ----------------------------
-- Records of supplier
-- ----------------------------
BEGIN;
INSERT INTO `supplier` VALUES (1, '供应商001', '为方便', '1232323', '史蒂夫');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
